/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import entity.Department;
import entity.Employee;
import entity.Holiday;
import entity.HolidayDetail;
import entity.LeaveDetail;
import entity.LeaveForm;
import entity.LeaveType;
import entity.OrganizationLevel;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Kien Chinh
 */
public class Request {

    Connection con;

    public Employee Login(String loginID, String pass) {
        Employee e = null;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                CallableStatement call = con.prepareCall("{call sp_login(?,?)}");
                call.setString(1, loginID);
                call.setString(2, pass);
                ResultSet rs = call.executeQuery();
                if (rs.next()) {
                    if (loginID.equals(rs.getString("LoginID")) && pass.equals(rs.getString("Password"))) {
                        e = new Employee();
                        e.setEmployeeID(rs.getInt(1));
                        e.setFullName(rs.getNString(2));
                        e.setBirthday(rs.getDate(3));
                        e.setGender(rs.getString(4));
                        e.setPhone(rs.getString(5));
                        e.setAddress(rs.getString(6));
                        Department d = new Department((rs.getString(7)));
                        ResultSet rsDepartment = stm.executeQuery("SELECT * FROM Department WHERE DepartmentID = '" + d.getDepartmentID() + "'");
                        rsDepartment.next();
                        d.setName(rsDepartment.getString(2));
                        e.setDepartment(d);

                        OrganizationLevel level = new OrganizationLevel(rs.getInt(8));
                        ResultSet rsLevel = stm.executeQuery("SELECT * FROM OrganizationLevel WHERE OrganizationLevelID = " + level.getOrganizationLevelID() + "");
                        rsLevel.next();
                        level.setName(rsLevel.getString(2));
                        e.setOrganization(level);
                    }
                }
                rs.close();
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return e;
    }

    public boolean addLeaveTransaction(LeaveForm form) {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            PreparedStatement insertDB;
            Statement selectDB;
            ResultSet rs;

            // insert leave form
            String query1 = "INSERT INTO LeaveForm (EmployeeID) VALUES (?)";
            // get LeaveFormID
            String query2 = "SELECT MAX(LeaveFormID) FROM LeaveForm";
            // insert LeaveDetail
            String query3 = "INSERT INTO LeaveDetail(LeaveFormID,TypeID,Startday,Finishday,[Status],Reson) VALUES (?,?,?,?,?,?)";

            //Start a transaction
            try {
                con.setAutoCommit(false);
                //1
                insertDB = con.prepareCall(query1);
                insertDB.setInt(1, form.getEmployee().getEmployeeID());
                insertDB.executeUpdate();
                insertDB.close();
                con.commit();

                //2
                selectDB = con.createStatement();
                rs = selectDB.executeQuery(query2);
                rs.next();
                form.setLeaveFormID(rs.getInt(1));
                rs.close();
                selectDB.close();
                con.commit();

                //3

                insertDB = con.prepareCall(query3);
                for (int i = 0; i < form.getListLeave().size(); i++) {
                    insertDB.setInt(1, form.getLeaveFormID());
                    insertDB.setInt(2, form.getListLeave().get(i).getType().getTypeID());
                    insertDB.setDate(3, new Date(form.getListLeave().get(i).getStartLeave().getTime()));
                    insertDB.setDate(4, new Date(form.getListLeave().get(i).getFinishLeave().getTime()));
                    insertDB.setInt(5, form.getListLeave().get(i).getStatus());
                    insertDB.setString(6, form.getListLeave().get(i).getReson());
                    insertDB.executeUpdate();
                    con.commit();
                }
                insertDB.close();
                con.setAutoCommit(true);
                return true;
            } catch (SQLException ex) {
                try {
                    con.rollback();
                    return false;
                } catch (SQLException ex1) {
                    Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex1);
                }
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public int CheckLeave(Employee e, LeaveType type) {
        int result = 0;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                CallableStatement call = con.prepareCall("{call sp_getLeaveHave(?,?)}");
                call.setInt(1, e.getEmployeeID());
                call.setInt(2, type.getTypeID());

                ResultSet rs = call.executeQuery();
                rs.next();
                result = rs.getInt(1);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public ArrayList getListLeaveType() {
        ArrayList<LeaveType> list = new ArrayList<>();
        String query = "SELECT * FROM LeaveType";
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                while (rs.next()) {
                    LeaveType type = new LeaveType();
                    type.setTypeID(rs.getInt(1));
                    type.setName(rs.getString(2));
                    type.setQuatity(rs.getInt(3));
                    list.add(type);
                }
                rs.close();
                stm.close();
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public int updateQuery(String query) {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                return stm.executeUpdate(query);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return -1;
    }

    public boolean executeCheckQuery(String query) {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                if (rs.next()) {
                    return true;
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return false;
    }

    public ArrayList<LeaveForm> getLeaveFormTransaction(int status, String departID) {
        ArrayList<LeaveForm> listForm = new ArrayList<>();

        String query1 = "SELECT DISTINCT LeaveForm.LeaveFormID FROM LeaveForm\n"
                + "JOIN LeaveDetail\n"
                + "ON LeaveForm.LeaveFormID = LeaveDetail.LeaveFormID\n"
                + "WHERE [Status] = ?";

        String query1withDepart = "SELECT DISTINCT LeaveForm.LeaveFormID FROM LeaveForm\n"
                + "JOIN LeaveDetail\n"
                + "ON LeaveForm.LeaveFormID = LeaveDetail.LeaveFormID\n"
                + "JOIN Employee\n"
                + "ON Employee.EmployeeID = LeaveForm.EmployeeID\n"
                + "JOIN Department\n"
                + "ON Employee.DepartmentID = Department.DepartmentID\n"
                + "WHERE [Status] = ? AND Employee.DepartmentID = ?";

        String query2 = "SELECT * FROM LeaveDetail\n"
                + "JOIN LeaveForm\n"
                + "ON LeaveDetail.LeaveFormID = LeaveForm.LeaveFormID\n"
                + "WHERE [Status] = ? AND LeaveForm.LeaveFormID = ?";

        String query3 = "SELECT LeaveType.TypeID,LeaveType.TypeName FROM LeaveType\n"
                + "JOIN LeaveDetail\n"
                + "ON LeaveType.TypeID = LeaveDetail.TypeID\n"
                + "WHERE LeaveDetailID = ?";

        String query4 = "SELECT * FROM Employee\n"
                + "JOIN LeaveForm\n"
                + "ON Employee.EmployeeID = LeaveForm.EmployeeID\n"
                + "AND LeaveForm.LeaveFormID = ?";

        String query5 = "SELECT * FROM Department\n"
                + "WHERE DepartmentID = ?";

        String query6 = "SELECT * FROM OrganizationLevel\n"
                + "WHERE OrganizationLevelID = ?";

        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                con.setAutoCommit(false);
                PreparedStatement pp;
                if (departID == null) {
                    pp = con.prepareCall(query1);
                } else {
                    pp = con.prepareCall(query1withDepart);
                    pp.setString(2, departID);
                }
                pp.setInt(1, status);
                ResultSet rsFormID = pp.executeQuery();
                con.commit();

                while (rsFormID.next()) {
                    LeaveForm form = new LeaveForm();
                    form.setLeaveFormID(rsFormID.getInt(1));

                    ArrayList<LeaveDetail> listDetail = new ArrayList<>();

                    pp = con.prepareCall(query2);
                    pp.setInt(1, status);
                    pp.setInt(2, form.getLeaveFormID());
                    ResultSet rsLeaveDetail = pp.executeQuery();
                    con.commit();

                    while (rsLeaveDetail.next()) {
                        LeaveDetail detail = new LeaveDetail();
                        detail.setLeaveDetailID(rsLeaveDetail.getInt(1));

                        LeaveType type = new LeaveType();
                        pp = con.prepareCall(query3);
                        pp.setInt(1, detail.getLeaveDetailID());
                        ResultSet rsLeaveType = pp.executeQuery();
                        con.commit();
                        rsLeaveType.next();

                        type.setTypeID(rsLeaveType.getInt(1));
                        type.setName(rsLeaveType.getString(2));

                        detail.setType(type);
                        detail.setStartLeave(rsLeaveDetail.getDate(4));
                        detail.setFinishLeave(rsLeaveDetail.getDate(5));
                        detail.setReson(rsLeaveDetail.getString(7));

                        listDetail.add(detail);
                    }
                    Employee employee = new Employee();
                    pp = con.prepareCall(query4);
                    pp.setInt(1, form.getLeaveFormID());
                    ResultSet rsEmployee = pp.executeQuery();
                    con.commit();
                    rsEmployee.next();

                    employee.setEmployeeID(rsEmployee.getInt(1));
                    employee.setFullName(rsEmployee.getString(2));
                    employee.setBirthday(rsEmployee.getDate(3));
                    employee.setGender(rsEmployee.getString(4));
                    employee.setPhone(rsEmployee.getString(5));
                    employee.setAddress(rsEmployee.getString(6));

                    Department department = new Department(rsEmployee.getString(7));
                    pp = con.prepareCall(query5);
                    pp.setString(1, department.getDepartmentID());
                    ResultSet rsDepartment = pp.executeQuery();
                    con.commit();
                    rsDepartment.next();
                    department.setName(rsDepartment.getString(2));
                    employee.setDepartment(department);

                    OrganizationLevel level = new OrganizationLevel(rsEmployee.getInt(8));
                    pp = con.prepareCall(query6);
                    pp.setInt(1, level.getOrganizationLevelID());
                    ResultSet rsLevel = pp.executeQuery();
                    con.commit();
                    rsLevel.next();
                    level.setName(rsLevel.getString(2));
                    employee.setOrganization(level);

                    form.setListLeave(listDetail);
                    form.setEmployee(employee);

                    listForm.add(form);
                }
                con.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return listForm;
    }

    public int getTaken(Employee e, int type, int status) {
        int result = 0;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                CallableStatement call = con.prepareCall("{call sp_countLeaveDetail(?,?,?)}");
                call.setInt(1, e.getEmployeeID());
                call.setInt(2, type);
                call.setInt(3, status);
                ResultSet rs = call.executeQuery();
                rs.next();
                result = rs.getInt(1);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public ArrayList<LeaveDetail> getLeaveDetail(Employee employee, String query) {
        ArrayList<LeaveDetail> list = new ArrayList<>();
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                PreparedStatement pp = con.prepareCall(query);
                pp.setInt(1, employee.getEmployeeID());
                ResultSet rsLeaveDetail = pp.executeQuery();
                while (rsLeaveDetail.next()) {
                    LeaveDetail detail = new LeaveDetail();
                    detail.setLeaveDetailID(rsLeaveDetail.getInt("LeaveDetailID"));
                    detail.setStartLeave(rsLeaveDetail.getDate("Startday"));
                    detail.setFinishLeave(rsLeaveDetail.getDate("Finishday"));
                    detail.setStatus(rsLeaveDetail.getInt("Status"));
                    detail.setReson(rsLeaveDetail.getString("Reson"));
                    LeaveType type = new LeaveType();
                    type.setTypeID(rsLeaveDetail.getInt("TypeID"));
                    type.setName(rsLeaveDetail.getString("TypeName"));
                    detail.setType(type);
                    detail.setCause(rsLeaveDetail.getString("Cause"));

                    list.add(detail);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public ArrayList getListHoliday() {
        ArrayList list = new ArrayList();
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery("SELECT * FROM HolidayDetail");
                while (rs.next()) {
                    HolidayDetail holi = new HolidayDetail();
                    holi.setHolidayDetailID(rs.getInt(1));
                    holi.setHolidayID(rs.getInt(2));
                    holi.setStartday(rs.getDate(3));
                    holi.setFinishday(rs.getDate(4));
                    list.add(holi);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public int countLeave(String query) {
        int result = 0;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                if (rs.next()) {
                    result = rs.getInt(1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public ArrayList<Employee> getListEmployee(Employee emLogin) {
        ArrayList<Employee> list = new ArrayList<>();
        String query1 = "SELECT * FROM Employee\n"
                + "WHERE DepartmentID != 'CEO'";
        String query4 = "SELECT * FROM Employee\n"
                + "WHERE DepartmentID = '" + emLogin.getDepartment().getDepartmentID() + "'";
        String query2 = "SELECT * FROM Department\n"
                + "WHERE DepartmentID = ?";
        String query3 = "SELECT * FROM OrganizationLevel\n"
                + "WHERE OrganizationLevelID = ?";
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                PreparedStatement pp;
                con.setAutoCommit(false);
                if (emLogin.getOrganization().getOrganizationLevelID() == 0) {
                    pp = con.prepareCall(query1);
                } else {
                    pp = con.prepareCall(query4);
                }
                ResultSet rsEmployee = pp.executeQuery();
                con.commit();
                while (rsEmployee.next()) {
                    Employee e = new Employee();
                    e.setEmployeeID(rsEmployee.getInt("EmployeeID"));
                    e.setFullName(rsEmployee.getString("FullName"));
                    e.setBirthday(rsEmployee.getDate("Birthday"));
                    e.setGender(rsEmployee.getString("Gender"));
                    e.setPhone(rsEmployee.getString("TelephoneNumber"));
                    e.setAddress(rsEmployee.getString("Address"));

                    Department department = new Department(rsEmployee.getString("DepartmentID"));
                    pp = con.prepareCall(query2);
                    pp.setString(1, department.getDepartmentID());
                    ResultSet rsDepartment = pp.executeQuery();
                    con.commit();
                    rsDepartment.next();
                    department.setName(rsDepartment.getString("DepartmentName"));

                    OrganizationLevel level = new OrganizationLevel(rsEmployee.getInt("OrganizationLevelID"));
                    pp = con.prepareCall(query3);
                    pp.setInt(1, level.getOrganizationLevelID());
                    ResultSet rsLevel = pp.executeQuery();
                    con.commit();
                    rsLevel.next();
                    level.setName(rsLevel.getString("OrganizationLevel"));

                    e.setDepartment(department);
                    e.setOrganization(level);

                    list.add(e);
                }
                con.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public void changePass(String pass, int id) {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                PreparedStatement updatePass = con.prepareStatement("UPDATE Account SET password = ? WHERE EmployeeID = " + id);
                updatePass.setString(1, pass);
                updatePass.executeUpdate();
                con.commit();

            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ArrayList<Department> getListDepartment() {
        ArrayList<Department> list = new ArrayList<>();
        String query = "SELECT * FROM Department";
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                while (rs.next()) {
                    Department de = new Department(rs.getString(1));
                    de.setName(rs.getString(2));
                    list.add(de);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public ArrayList<OrganizationLevel> getListOrganizationLevel() {
        ArrayList<OrganizationLevel> list = new ArrayList<>();
        String query = "SELECT * FROM OrganizationLevel";
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                while (rs.next()) {
                    OrganizationLevel og = new OrganizationLevel(rs.getInt(1));
                    og.setName(rs.getString(2));
                    list.add(og);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public int getMaxSmt(String query) {
        int result = 0;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(query);
                rs.next();
                result = rs.getInt(1);
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return result;
    }

    public boolean addHoliday(Holiday h) {
        String query1 = "INSERT Holiday ([Description]) VALUES ('?')";
        String query2 = "SELECT MAX(HolidayID) FROM Holiday";
        String query3 = "INSERT HolidayDetail (HolidayID,Startday,Finishday) VALUES (?,'?','?')";
        PreparedStatement pp;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                con.setAutoCommit(false);
                pp = con.prepareCall(query1);
                pp.setString(1, h.getDescription());
                pp.executeUpdate();
                con.commit();

                pp = con.prepareCall(query2);
                ResultSet rs = pp.executeQuery();
                con.commit();
                rs.next();
                h.setHolidayID(rs.getInt(1));

                for (int i = 0; i < h.getList().size(); i++) {
                    pp = con.prepareCall(query3);
                    pp.setInt(1, h.getHolidayID());
                    pp.setDate(2, new java.sql.Date(h.getList().get(i).getStartday().getTime()));
                    pp.setDate(3, new java.sql.Date(h.getList().get(i).getFinishday().getTime()));
                    pp.executeUpdate();
                    con.commit();
                }
                con.setAutoCommit(true);
                return true;
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public void updateLeaveOutOfDate() {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                CallableStatement call = con.prepareCall("{call sp_checkLeaveConfirm}");
                call.executeUpdate();
            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean addEmployee(Employee employee) {
        java.sql.Date birthday = new java.sql.Date(employee.getBirthday().getTime());
        String query1 = "INSERT INTO Employee VALUES ('" + employee.getFullName() + "','" + birthday + "','" + employee.getGender() + "','" + employee.getPhone() + "','" + employee.getAddress() + "','" + employee.getDepartment().getDepartmentID() + "'," + employee.getOrganization().getOrganizationLevelID() + ")";
        String query2 = "SELECT MAX(Employee.EmployeeID) FROM Employee";
        String query3 = "INSERT INTO Account VALUES ('" + employee.getAccount().getLoginID() + "'," + '?' + "," + employee.getAccount().getPassword() + ")";
        String query4 = "SELECT * FROM Account WHERE LoginID = '"+employee.getAccount().getLoginID()+"'";
        PreparedStatement pp;
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                con.setAutoCommit(false);
                pp = con.prepareCall(query1);
                pp.executeUpdate();
                con.commit();

                pp = con.prepareCall(query2);
                ResultSet rs = pp.executeQuery();
                con.commit();
                rs.next();
                employee.setEmployeeID(rs.getInt(1));

                pp = con.prepareCall(query4);
                rs = pp.executeQuery();
                con.commit();

                if (!rs.next()) {
                    pp = con.prepareCall(query3);
                    pp.setInt(1, employee.getEmployeeID());
                    pp.executeUpdate();
                    con.commit();
                }else{
                    JOptionPane.showMessageDialog(null, "LoginID is duplicate", "Error", JOptionPane.ERROR_MESSAGE);
                    con.rollback();
                }
                con.setAutoCommit(true);
                return true;
            } catch (SQLException ex) {
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                    Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex1);
                }
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }
    
    public void changeInfo(int id , String number , String address) {
        if (con == null) {
            con = ConnectDB.getConnect();
        }
        if (con != null) {
            try {
                PreparedStatement updateInfo = con.prepareStatement("UPDATE Employee SET TelephoneNumber ='"+number+"' , Address = '"+address+"' WHERE EmployeeID = " + id);
                updateInfo.executeUpdate();
                con.commit();

            } catch (SQLException ex) {
                Logger.getLogger(Request.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
